
--EXEC SP_CREATE_UPDATE_RMA_RECEIVE_WIP_LOT 1,1,1017,62,'lotNo1','fablotNo1',1000, '09/11/2008', 'OSEP', 'FT', null, 'System Admin'
ALTER PROCEDURE SP_CREATE_UPDATE_RMA_RECEIVE_WIP_LOT
 (
@RMA_RECEIVE_ID INT,
@RMA_RECEIVE_LOT_ID INT,
@FG_PROCESSSTEP_ID INT,
@FG_LOCATION_ID    INT,
@LOT_NUMBER     VARCHAR(50),
@DATE_CODE      VARCHAR(50),
@DIE_QTY_IN		INT,
@DATE_IN		DATETIME,
@NEXT_LOCATION  VARCHAR(50),
@NEXT_PROCESS   VARCHAR(50),
@COMMENTS		TEXT,
@USER_NAME		VARCHAR(50)

)
AS
BEGIN
		DECLARE @INVENTORY_TYPE VARCHAR(50)
		DECLARE @PROCESS_STEP_ID INT
		DECLARE @LOCATION_ID INT
		DECLARE @NEW_LOT_NUMBER VARCHAR(50)
		DECLARE @NEW_LOT_ID INT
		DECLARE @REMAIN_RMA_RECEIVE_QTY INT

		IF @LOT_NUMBER IS NOT NULL		
		BEGIn
			SET @NEW_LOT_NUMBER = @LOT_NUMBER + 'Z'
		END
		
		IF @NEXT_PROCESS = 'FT' OR @NEXT_PROCESS = 'FPO' 
		BEGIN
				SET @INVENTORY_TYPE = 'TEST'
		END
		ELSE IF @NEXT_PROCESS = 'Tape & Reel'  
		BEGIN
				SET @INVENTORY_TYPE = 'Class Store'
		END
		ELSE IF @NEXT_PROCESS = 'FG'  
		BEGIN
				SET @INVENTORY_TYPE = 'Finished Goods'
		END
		ELSE
		BEGIN
				SET @INVENTORY_TYPE = @NEXT_PROCESS
		END
		
		SELECT TOP 1 @PROCESS_STEP_ID = PROCESSSTEP_ID FROM FN_GET_PROCESS_FLOWS_BY_PROCESSSTEP_ID( @FG_PROCESSSTEP_ID,0 )  WHERE PS_TYPE = @NEXT_PROCESS
		
		IF @PROCESS_STEP_ID IS NULL
		BEGIN
				UPDATE LOT_TABLE SET LOT_NUMBER = 'LOT_NUMBER' WHERE LOT_NUMBER = 'LOT_NUMBER'
				RETURN
		END
		SELECT @LOCATION_ID = LOCATION_ID FROM LOCATION WHERE L_TYPE = @INVENTORY_TYPE AND L_VENDOR_NAME = @NEXT_LOCATION
		
		SELECT @NEW_LOT_ID = LOT_ID FROM LOT_TABLE WHERE PROCESSSTEP_ID = @PROCESS_STEP_ID AND LOCATION_ID = @LOCATION_ID AND LOT_NUMBER = @NEW_LOT_NUMBER 
		
		IF @PROCESS_STEP_ID IS NOT NULL AND @LOCATION_ID IS NOT NULL AND @NEW_LOT_ID IS NULL
		BEGIN
			INSERT INTO Lot_Table (ProcessStep_ID, Location_ID, Lot_Number, Fab_Lot_Number, Vendor_Lot_Number, qty_in,  Date_In, Lot_Current_Status, Timestamp,Lot_Entered_By, Lot_Category, LOT_DATE_CODE, LOT_DROP_OUT, USER_NAME) 
					VALUES (@PROCESS_STEP_ID, @LOCATION_ID, @NEW_LOT_NUMBER, @NEW_LOT_NUMBER, @NEW_LOT_NUMBER,  @DIE_QTY_IN,  @DATE_IN, 'Running', CURRENT_TIMESTAMP, 0, 'PROD', @DATE_CODE, 0 , @USER_NAME )
			
			SELECT @NEW_LOT_ID = @@IDENTITY

			IF @INVENTORY_TYPE = 'Die Bank' OR @INVENTORY_TYPE = 'Class Store'
			BEGIN
				INSERT INTO Inventory_Lot( Lot_ID, Timestamp ) values (@NEW_LOT_ID, CURRENT_TIMESTAMP )		
			END
			ELSE IF @INVENTORY_TYPE = 'Finished Goods'
			BEGIN
				
				INSERT INTO Inventory_Lot( Lot_ID, Timestamp, IL_Date_In, IL_Qty_In, IL_QTY_Balance ) values (@NEW_LOT_ID, CURRENT_TIMESTAMP, @DATE_IN, @DIE_QTY_IN, @DIE_QTY_IN )		
			END
			ELSE IF @INVENTORY_TYPE = 'Assembly' 
			BEGIN
				
				INSERT INTO Assembly_Lot( Lot_ID, Timestamp ) values (@NEW_LOT_ID, CURRENT_TIMESTAMP)		
			END
			ELSE IF @INVENTORY_TYPE = 'TEST' 
			BEGIN
				INSERT INTO Final_Test_Lot( Lot_ID, Timestamp ) values (@NEW_LOT_ID, CURRENT_TIMESTAMP)		
			END
			
		END
		ELSE 
		BEGIN	
				IF @INVENTORY_TYPE = 'Die Bank' OR @INVENTORY_TYPE = 'Class Store'
				BEGIN
					UPDATE Inventory_Lot SET TIMESTAMP = CURRENT_TIMESTAMP WHERE LOT_ID = @NEW_LOT_ID
				END
				ELSE IF @INVENTORY_TYPE = 'Finished Goods'
				BEGIN
					UPDATE Inventory_Lot SET IL_QTY_IN = IL_QTY_IN + @DIE_QTY_IN, IL_QTY_BALANCE = IL_QTY_BALANCE + @DIE_QTY_IN , TIMESTAMP = CURRENT_TIMESTAMP WHERE LOT_ID = @NEW_LOT_ID
				END
				ELSE IF @INVENTORY_TYPE = 'Assembly' 
				BEGIN
					UPDATE Assembly_Lot SET TIMESTAMP = CURRENT_TIMESTAMP WHERE LOT_ID = @NEW_LOT_ID
				END
				ELSE IF @INVENTORY_TYPE = 'TEST' 
				BEGIN
					UPDATE Final_Test_Lot SET TIMESTAMP = CURRENT_TIMESTAMP WHERE LOT_ID = @NEW_LOT_ID	
				END
			
				UPDATE LOT_TABLE SET Lot_Current_Status = 'Running', QTY_IN = QTY_IN + @DIE_QTY_IN, USER_NAME = @USER_NAME WHERE LOT_ID = @NEW_LOT_ID
		END
		
		INSERT INTO RMA_DISPOSE_TABLE ( Lot_Id, Lot_Number, Qty_In, RMA_Receive_Lot_Id, Timestamp, updated_by )
		VALUES ( @NEW_LOT_ID, @NEW_LOT_NUMBER, @DIE_QTY_IN, @RMA_RECEIVE_LOT_ID, CURRENT_TIMESTAMP, @USER_NAME )
		
		UPDATE RMA_RECEIVE_LOT_TABLE SET QTY_IN = QTY_IN - @DIE_QTY_IN , TIMESTAMP = CURRENT_TIMESTAMP WHERE RMA_RECEIVE_LOT_ID = @RMA_RECEIVE_LOT_ID

		SELECT @REMAIN_RMA_RECEIVE_QTY = ISNULL(QTY_IN, 0) FROM RMA_RECEIVE_LOT_TABLE WHERE RMA_RECEIVE_LOT_ID = @RMA_RECEIVE_LOT_ID

		IF @REMAIN_RMA_RECEIVE_QTY = 0
		BEGIN
			UPDATE RMA_RECEIVE_LOT_TABLE SET Status = 'Dispose', TIMESTAMP = CURRENT_TIMESTAMP WHERE RMA_RECEIVE_LOT_ID = @RMA_RECEIVE_LOT_ID	
		END

		IF ( SELECT COUNT(*) FROM RMA_RECEIVE_LOT_TABLE WHERE RMA_RECEIVE_ID = @RMA_RECEIVE_ID AND STATUS = 'Receive' ) = 0
		BEGIN
				UPDATE RMA_RECEIVE_TABLE SET RMA_STATUS = 'Close' WHERE RMA_RECEIVE_ID = @RMA_RECEIVE_ID
		END
END


